
IF EXISTS (SELECT 1
          FROM SYSOBJECTS
          WHERE  id = OBJECT_ID('USP_List_staff_report1')
          AND TYPE IN ('P','PC'))
DROP PROCEDURE USP_List_staff_report1
GO
/****** Object:  StoredProcedure [dbo].[USP_List_staff_report1]    Script Date: 11/22/2010 02:03:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		AKE
-- Create date: 17:06 18/9/2010
-- =============================================
CREATE PROCEDURE [dbo].[USP_List_staff_report1]
	-- Add the parameters for the stored procedure here
	--@group_user_code int,
	@department_code nvarchar(256),
	@staff_status int,
	@ctx_staff_code int,
	@ctx_department_code int
AS
BEGIN
		SET NOCOUNT ON;
	--declare @ctx_department_code int

	declare @master_department_level as int
	declare @table as table(department_code int)
	declare @level as int

	select @master_department_level = mas.master_department_level 
	from stp_department dep 
	inner join master_department mas on dep.master_department_code = mas.master_department_code
	where department_code = @ctx_department_code

	--select @ctx_department_code = department_code from staff where staff_code = @ctx_staff_code

	if @ctx_department_code = 0
	begin
		
		if @department_code = '0'
		begin
			insert @table 
			select department_code from stp_department where department_code >= @ctx_department_code and @department_code in (0, department_code)
		end
		else
		begin
			insert @table 
			select department_code from stp_department where department_code >= @ctx_department_code and department_code in (
				select 
						number
				from
						dbo.fnIter_intlist_to_table(@department_code)
			)
		end
	end
	else
	begin
		if @department_code = '0'
		begin
			insert @table 
			select department_code from stp_department where @ctx_department_code in (0,department_code)
		end
		else
		begin
			insert @table 
			select department_code from stp_department where department_code in(
				select 
						number
				from
						dbo.fnIter_intlist_to_table(@department_code)
			)
		end
	end
		
	set @level = @master_department_level
	while @level < 10
	begin
		insert @table 
		select department_code 
		from stp_department 
		where department_parent in (select department_code from @table) 
		
		set @level = @level + 1
	end

	select 
			s.staff_code , 
			s.staff_no ,
			fullname = isnull(s.staff_firstname,'') + ' ' + isnull(s.staff_lastname,'') ,
			gu.group_user_name ,
			sd.department_code,
			department_hirachy = sd.department_name,
			--department_hirachy = dbo.UFN_GetSTPDepartmentHirachy(s.department_code)  ,
			[status] = CASE s.[staff_status]	WHEN 1 THEN 'Y'
												WHEN 2 THEN 'N'
					   END
						 ,
			SeqNo = ROW_NUMBER() over(order by s.staff_code),
			[staff_code_update] = (select staff_no from staff as ss where ss.staff_code = s.staff_code_update),
			sd.department_parent ,
			department_parent_name = sd.department_name,
			md.master_department_level
	from	staff as s
	left join group_user as gu on s.group_user_code = gu.group_user_code
	left join stp_department as sd on s.department_code = sd.department_code
	left join master_department as md on sd.master_department_code = md.master_department_code 
	where	
	--Filter status
	@staff_status in (0,s.staff_status) and
	sd.department_code in  (select department_code from @table where department_code is not null) and
	sd.department_code > 0

END
GO
